-- @owner: opentestcase043
-- @date: 2023/2/17
-- @testpoint: 共享存储支持同义词测试

--step1: 创建表; expect: 创建表成功
drop table if exists t_storge_synonym_0001;
create table t_storge_synonym_0001 (a int,b int) with (segment=on);

--step2: 创建表的同义词; expect: 创建表同义词成功
drop synonym if exists syn_t_storge_synonym_0001;
create or replace synonym syn_t_storge_synonym_0001 for t_storge_synonym_0001;

--step3: 通过同义词操作表数据; expect:成功
select * from syn_t_storge_synonym_0001;
insert into syn_t_storge_synonym_0001 values(1,'777');
insert into syn_t_storge_synonym_0001 values(2,'55');
insert into syn_t_storge_synonym_0001 values(3,'23');
insert into syn_t_storge_synonym_0001 values(4,'21');
insert into  syn_t_storge_synonym_0001 values (generate_series(5, 10));
update syn_t_storge_synonym_0001 set a=3 where b=21;
delete syn_t_storge_synonym_0001 where a=3;
select count(*) from syn_t_storge_synonym_0001;

--step4: 创建视图; expect: 创建视图成功
drop view if exists v_storge_synonym_0001;
create or replace view v_storge_synonym_0001 as select * from syn_t_storge_synonym_0001;

--step5: 创建视图的同义词; expect: 创建视图同义词成功
drop synonym if exists syn_v_storge_synonym_0001;
create or replace synonym syn_v_storge_synonym_0001 for v_storge_synonym_0001;

--step6:查看视图同义词数据; expect: 使用视图同义词成功
select count(*) from  syn_v_storge_synonym_0001;

--step7: 创建重载函数其同义词; expect: 创建成功
drop function if exists func_storge_synonym_0001;
drop synonym if exists  syn_func_storge_synonym_0001;
create or replace function func_storge_synonym_0001(a integer, b integer) returns integer as
$$
select $1 + $2
$$
language sql;
/
create or replace synonym syn_func_storge_synonym_0001 for func_storge_synonym_0001;

--step8: 使用同义词; expect: 成功
select syn_func_storge_synonym_0001(1,2);

--step9: 创建存储过程及其同义词; expect: 创建成功
drop procedure if exists pro_storge_synonym_0001;
drop synonym if exists syn_pro_storge_synonym_0001;
create procedure pro_storge_synonym_0001(n_id integer, n_name varchar2(10))
security invoker
as
begin
    insert into t_storge_synonym_0001 values(2,3);
end;
/

create or replace synonym syn_pro_storge_synonym_0001 for pro_storge_synonym_0001;

--step10: 使用同义词调用存储过程 expect: 调用成功
call syn_pro_storge_synonym_0001(2,3);

--step11: 清理环境; expect: 清理环境成功
drop view if exists v_storge_synonym_0001;
drop synonym if exists syn_t_storge_synonym_0001;
drop synonym if exists syn_v_storge_synonym_0001;
drop function if exists func_storge_synonym_0001;
drop synonym if exists syn_func_storge_synonym_0001;
drop procedure if exists pro_storge_synonym_0001;
drop synonym if exists syn_pro_storge_synonym_0001;
drop table if exists t_storge_synonym_0001;